using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using Microsoft.ApplicationBlocks.Data;
using MCMLXVII.BackEnd.Data.OrganizationChart.DataSets;
using System.Data;
using MCMLXVII.BackEnd.Services.Common.Messages;
using System.ServiceModel;

namespace MCMLXVII.BackEnd.Data.OrganizationChart
{
    public static class DALDistributionLists
    {

        public static DSOrganizationTreeNewItem.NewCreatedItemRow CreateNewDistributionList(string DistributionListName, int CreatorMemberID )
        {
            DSOrganizationTreeNewItem.NewCreatedItemDataTable dt = new DSOrganizationTreeNewItem.NewCreatedItemDataTable();
            dt.Load(SqlHelper.ExecuteReader("dbo.CreateNewDistributionList", (object)DistributionListName, CreatorMemberID ));
            if (dt.Rows.Count > 0)
                return (DSOrganizationTreeNewItem.NewCreatedItemRow)dt.Rows[0];
            else
                return null;
        }

        public static int UpdateDistributionList(int DistributionListID, string Name, int UpdateSequence, int UpdateMemberID)
        {
            return (int)SqlHelper.ExecuteScalar("dbo.UpdateDistributionList", DistributionListID, Name, UpdateSequence, UpdateMemberID);
        }

        public static int UpdateDistributionListMembership(int DistributionListID, int[] Members, bool[] Recursive, int UpdateSequence, int UpdateMemberID)
        {
            int NewUpdateSequence = 0;
            try
            {

                NewUpdateSequence = (int)SqlHelper.ExecuteScalar("dbo.UpdateDistributionListMembership_A", DistributionListID, UpdateSequence, UpdateMemberID);

                for (int i = 0; i < Members.Length; i++ )
                {
                    string C = "C";
                    if (Recursive[i])
                        C = "R";
                    SqlHelper.ExecuteNonQuery("dbo.UpdateDistributionListMembership_B", DistributionListID, Members[i], C  );
                }
            }
            catch (DbException ex)
            {
                if (ex.ErrorCode == 500001)
                {
                    throw new FaultException<BackendFault>(new BackendFault(BackendFaulType.DataUpdateSequence ));
                }
                else
                {
                    throw new FaultException<BackendFault>(new BackendFault(BackendFaulType.Technical, ex.Message ));
                }
            }
            return NewUpdateSequence;
        }


        public static bool DeleteDistributionList(int DistributionListID, int DeleteMemberID)
        {
            try
            {
                SqlHelper.ExecuteNonQuery("dbo.DeleteDistributionList", DistributionListID, DeleteMemberID);
                return true;
            }
            catch
            {
                return false;
            }

        }

        public static DSDistributionLists GetDistributionList(int DistributionListID, bool IncludeMembers)
        {
                DSDistributionLists ds = new DSDistributionLists();
                ds.Tables["DistributionLists"].Load(SqlHelper.ExecuteReader(CommandType.Text, "select DistributionListID, [Name], cast(UpdateSequence as int) as UpdateSequence from DistributionLists where DistributionlistID = " + DistributionListID.ToString()));
                if (IncludeMembers)
                    ds.Tables["DistributionListsMembers"].Load(SqlHelper.ExecuteReader(CommandType.Text, "select DistributionListID, MemberID, IncludeMode from DistributionListsMembers where DistributionlistID = " + DistributionListID.ToString()));
                return ds;
        }

        public static DSDistributionLists.DistributionListsDataTable GetDistributionLists()
        {
            DSDistributionLists.DistributionListsDataTable dt = new DSDistributionLists.DistributionListsDataTable();
            dt.Load(SqlHelper.ExecuteReader(CommandType.Text, "select DistributionListID, [Name], cast(UpdateSequence as int) as UpdateSequence from DistributionLists"));
            return dt;
        }


        public static DSDistributionLists.DistributionsListListDataTable GetDistributionListsChanged(int SinceExpansionID)
        {
            DSDistributionLists.DistributionsListListDataTable dt = new DSDistributionLists.DistributionsListListDataTable();
            dt.Load(SqlHelper.ExecuteReader("dbo.GetDistributionsListChanged", SinceExpansionID));
            return dt;
        }

        
        public static DSDistributionLists.DistributionListExpandedContentDataTable  GetDistributionListExpandedContents(int DistributionListID)
        {
                DSDistributionLists.DistributionListExpandedContentDataTable dt = new DSDistributionLists.DistributionListExpandedContentDataTable();
                dt.Load(SqlHelper.ExecuteReader("dbo.GetDistributionListExpandedContent", DistributionListID ));                
                return dt;
        }


        public static int CreateExpansion()
        {
            return (int)SqlHelper.ExecuteScalar("dbo.CreateDistributionListsExpansion");
        }

        public static int GetLastExpansion()
        {
            return (int)SqlHelper.ExecuteScalar("select max(ExpansionID) from DistributionListsExpansions");
        }

        
        public static DSDistributionLists.DistributionListLoginsDataTable GetDistributionListLogins(int[] DistributionLists)
        {
            string sti = "(";
            foreach (int i in DistributionLists)
            {
                sti += i.ToString() + ", ";
            }
            sti = sti.Substring(0, sti.Length - 2);
            sti += ")"; 
            string st = "declare @ExpansionID int ";
            st += "select @ExpansionID = max(ExpansionID) from DistributionListsExpansions ";            
            st += "select distinct SecurityIdentifier as [Login] from DistributionListsExpanded where ";
            st += " ExpansionID = @ExpansionID and DistributionListID in " + sti; 
	
            DSDistributionLists.DistributionListLoginsDataTable  dt = new DSDistributionLists.DistributionListLoginsDataTable();
            dt.Load(SqlHelper.ExecuteReader(CommandType.Text, st ));
            return dt;
        }

        
        public static DSDistributionLists.DistributionListLoginsDeltaDataTable GetDistributionListLoginsDelta(int[] DistributionLists, int FromExpansionID)
        {
            string sti = "(";
            foreach (int i in DistributionLists)
            {
                sti += i.ToString() + ", ";
            }
            sti = sti.Substring(0, sti.Length - 2);
            sti += ") ";

            string st = "declare @ExpansionID int \n";
            st += "declare @TempAnt TABLE([Login] varchar(64)) ";
            st += "declare @TempAct TABLE([Login] varchar(64)) ";
            st += "insert into @TempAnt ";
            st += "select distinct SecurityIdentifier from DistributionListsExpanded where ";
            st += " ExpansionID = " + FromExpansionID.ToString() + " and DistributionListID in " + sti +" "; 
            st += "select @ExpansionID = max(ExpansionID) from DistributionListsExpansions ";
            st += "insert into @TempAct ";
            st += "select distinct SecurityIdentifier from DistributionListsExpanded where ";
            st += " ExpansionID = @ExpansionID and DistributionListID in " + sti + " "; 
            st += "select [Login], 'A' as Added from @TempAct DLE ";
            st += "where not exists (select * from @TempAnt DLE2 where ";
            st += "                  DLE2.Login = DLE.Login)";
            st += "union ";
            st += "select [Login], 'D' as Added from @TempAnt DLE ";
            st += "where not exists (select * from @TempAct DLE2 where ";
            st += "                  DLE2.Login = DLE.Login)";
	
            DSDistributionLists.DistributionListLoginsDeltaDataTable  dt = new DSDistributionLists.DistributionListLoginsDeltaDataTable();
            dt.Load(SqlHelper.ExecuteReader(CommandType.Text, st ));
            return dt;
        }

    }
}
